﻿using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Wordprocessing;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Data;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace Common.Framework.Core.Npoi
{
    public static class NpoiHelper
    {



        #region 从excel文件中将数据导出到datatable/datatable
        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <param name="dir">excel列名和DataTable列名的对应字典</param>
        /// <returns></returns>
        public static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, Dictionary<string, string> dir = null)
        {
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            //没有标头或者不需要表头用excel列的序号（1,2,3..）作为DataTable的列名
            if (HeaderRowIndex < 0)
            {
                headerRow = sheet.GetRow(0);
                cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    DataColumn column = new DataColumn(Convert.ToString(i));
                    table.Columns.Add(column);
                }
            }
            //有表头，使用表头做为DataTable的列名
            else
            {
                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    //如果excel某一列列名不存在：以该列的序号作为Datatable的列名，如果DataTable中包含了这个序列为名的列，那么列名为重复列名+序号
                    if (headerRow.GetCell(i) == null)
                    {
                        if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(Convert.ToString(i));
                            table.Columns.Add(column);
                        }

                    }
                    //excel中的某一列列名不为空，但是重复了：对应的Datatable列名为“重复列名+序号”
                    else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                    {
                        DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                        table.Columns.Add(column);
                    }
                    else
                    //正常情况，列名存在且不重复：用excel中的列名作为datatable中对应的列名
                    {
                        string colName = headerRow.GetCell(i).ToString();
                        if (dir != null)
                        {
                            colName = dir.Where(s => s.Value == colName).First().Key;

                        }
                        DataColumn column = new DataColumn(colName);
                        table.Columns.Add(column);
                    }
                }
            }
            int rowCount = sheet.LastRowNum;
            for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍历
            {
                IRow row;
                if (sheet.GetRow(i) == null)//如果excel有空行，则添加缺失的行
                {
                    row = sheet.CreateRow(i);
                }
                else
                {
                    row = sheet.GetRow(i);
                }
                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍历
                {
                    if (row.GetCell(j) != null)
                    {
                        switch (row.GetCell(j).CellType)
                        {
                            case CellType.String://字符串
                                string str = row.GetCell(j).StringCellValue;
                                if (str != null && str.Length > 0)
                                {
                                    dataRow[j] = str.ToString();
                                }
                                else
                                {
                                    dataRow[j] = default(string);
                                }
                                break;
                            case CellType.Numeric://数字
                                if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//时间戳数字
                                {
                                    dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                }
                                else
                                {
                                    dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                }
                                break;
                            case CellType.Boolean:
                                dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                break;
                            case CellType.Error:
                                dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                break;
                            case CellType.Formula://公式
                                switch (row.GetCell(j).CachedFormulaResultType)
                                {
                                    case CellType.String:
                                        string strFORMULA = row.GetCell(j).StringCellValue;
                                        if (strFORMULA != null && strFORMULA.Length > 0)
                                        {
                                            dataRow[j] = strFORMULA.ToString();
                                        }
                                        else
                                        {
                                            dataRow[j] = null;
                                        }
                                        break;
                                    case CellType.Numeric:
                                        dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                        break;
                                    case CellType.Boolean:
                                        dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                        break;
                                    case CellType.Error:
                                        dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                        break;
                                    default:
                                        dataRow[j] = "";
                                        break;
                                }
                                break;
                            default:
                                dataRow[j] = "";
                                break;
                        }
                    }

                }
                table.Rows.Add(dataRow);
            }
            return table;
        }

        /// <summary>
        /// 读取Excel文件特定名字sheet的内容到DataTable
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="SheetName">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <param name="dir">excel列名和DataTable列名的对应字典</param>
        /// <returns></returns>
        public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, string SheetName, int HeaderRowIndex = 1)
        {
            DataTable table = new DataTable();
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (file.Length > 0)
                {
                    IWorkbook wb = WorkbookFactory.Create(file);
                    ISheet isheet = wb.GetSheet(SheetName);
                    table = ImportDt(isheet, HeaderRowIndex, dir);
                    isheet = null;
                }
            }
            return table;
        }

        /// <summary>
        /// 读取Excel文件某一索引sheet的内容到DataTable
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="SheetIndex">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <param name="dir">excel列名和DataTable列名的对应字典</param>
        /// <returns></returns>
        public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, int HeaderRowIndex = 1, int SheetIndex = 0)
        {
            DataTable table = new DataTable();
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (file.Length > 0)
                {
                    IWorkbook wb = WorkbookFactory.Create(file);
                    ISheet isheet = wb.GetSheetAt(SheetIndex);
                    table = ImportDt(isheet, HeaderRowIndex, dir);
                    isheet = null;
                }
            }
            return table;

        }

        /// <summary>
        /// 读取excel 文档, 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <param name="titleRowNumber">表头所在的行,默认为第一行</param>
        /// <returns></returns>
        public static DataTable ImportFromFile(string strFileName, int titleRowNumber = 0)
        {
            DataTable dt = new DataTable();
            IWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                string fileExt = Path.GetExtension(strFileName);
                if (fileExt == ".xls")
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                else if (fileExt == ".xlsx")
                {
                    hssfworkbook = new XSSFWorkbook(file);
                }
                else
                {
                    return new DataTable();
                }
            }

            return Workbook2DataTableCommon(titleRowNumber, dt, hssfworkbook);
        }

        /// <summary>
        /// 读取excel 文档流, 默认第一行为标头
        /// </summary>
        /// <param name="fileType">文档类型</param>
        /// <param name="fileStream">文档流</param>
        /// <param name="titleRowNumber">表头所在的行,默认为第一行</param>
        /// <returns></returns>
        public static DataTable ImportFormStream(ExcelFileType fileType, Stream fileStream, int titleRowNumber = 0)
        {
            DataTable dt = new DataTable();
            IWorkbook hssfworkbook;
            switch (fileType)
            {
                case ExcelFileType.xlsx:
                    hssfworkbook = new XSSFWorkbook(fileStream);
                    break;
                case ExcelFileType.xls:
                    hssfworkbook = new HSSFWorkbook(fileStream);
                    break;
                default:
                    return dt;
            }
            return Workbook2DataTableCommon(titleRowNumber, dt, hssfworkbook);
        }
        /// <summary>
        /// 读取excel 文档流, 默认第一行为标头
        /// </summary>
        /// <param name="contentType">文档类型</param>
        /// <param name="fileStream">文档流</param>
        /// <param name="titleRowNumber">表头所在的行,默认为第一行</param>
        /// <returns></returns>
        public static DataTable ImportFormStream(string contentType, Stream fileStream, int titleRowNumber = 0)
        {
            DataTable dt = new DataTable();
            IWorkbook hssfworkbook;
            switch (contentType)
            {
                case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
                    hssfworkbook = new XSSFWorkbook(fileStream);
                    break;
                case "application/vnd.ms-excel":
                    hssfworkbook = new HSSFWorkbook(fileStream);
                    break;
                default:
                    return dt;
            }
            return Workbook2DataTableCommon(titleRowNumber, dt, hssfworkbook);
        }
        private static DataTable Workbook2DataTableCommon(int titleRowNumber, DataTable dt, IWorkbook hssfworkbook)
        {
            ISheet sheet = hssfworkbook.GetSheetAt(0);

            IRow headerRow = sheet.GetRow(titleRowNumber);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1 + titleRowNumber); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    try
                    {
                        if (row.GetCell(j) != null)
                        {


                            if (row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy/MM/dd HH:mm :ss");
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();

                            }

                        }
                    }
                    catch
                    {
                        continue;
                    }
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        #endregion


        #region 操作excel
        /// <summary>
        /// 返回文件流
        /// </summary>
        /// <param name="directoryName">目录名称</param>
        /// <param name="filename">文件名称filename</param>
        /// <param name="dataTable">dataTable</param>
        /// <returns></returns>
        public static FileStream FileStream(string directoryName, string filename, DataTable dataTable)
        {
            if (!Directory.Exists(directoryName))
            {
                Directory.CreateDirectory(directoryName);
            }
            string fullPath = $"{directoryName}\\{filename}";
            string name = "";
            using (FileStream fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
            {
                var buf = TableToExcel(dataTable, fullPath);
                fs.Write(buf, 0, buf.Length);
                name = fs.Name;
                fs.Flush();
            }
            var stream = File.OpenRead(name);
            return stream;
        }

        /// <summary>
        /// 导出excel的html格式
        /// </summary>
        /// <param name="title">导出列名</param>
        /// <param name="contentJson">导出内容</param>
        /// <returns></returns>
        public static Stream ExportBigDataHtml<T>(string[] title, List<T> data, int skip, int take)
        {
            var sphtml = new StringBuilder();

            #region 导出标题

            if (skip == 0)
            {
                sphtml.Append("<table border=\"1px\" style=\"text-align: left; font-size: 10pt\" cellpadding=\"0\" cellspacing=\"0\">");
                sphtml.Append("<tr style=\"background-color: yellow;\">");
                sphtml.Append("<td align=\"center\"><b>序号</b></td>");
                foreach (var titleItem in title)
                {
                    sphtml.Append(string.Format("<td align=\"center\">{0}</td>", titleItem));
                }
                sphtml.Append("</tr>");
            }

            #endregion 导出标题

            #region 导出内容

            for (var i = skip; i < skip + take; i++)
            {
                sphtml.Append("<tr>");
                sphtml.Append(string.Format("<td align=\"center\">{0}</td>", (i + 1)));
                IDictionary<String, Object> item = (IDictionary<String, Object>)data[i];
                int propertyCount = item.Count;

                foreach (var contentItem in item)
                {
                    var value = contentItem.Value ?? "";
                    var writeContent = string.Empty;
                    if (value.GetType() == typeof(DateTime))
                        writeContent = ((DateTime)value).ToString("yyyy/MM/dd HH:mm:ss");
                    else
                        writeContent = value.ToString().Replace("\"", "");
                    sphtml.Append(string.Format("<td align=\"center\">{0}</td>", writeContent));
                }
                sphtml.Append("</tr>");
            }

            if ((skip + take) == data.Count)
            {
                sphtml.Append("</table>");
            }

            #endregion 导出内容

            var buffer = Encoding.Default.GetBytes(sphtml.ToString());
            Stream output = new MemoryStream(buffer);
            return output;
        }

        /// <summary>
        /// Dictionary<DataTable, List<NPOI.SS.Util.CellRangeAddress>> 导出到Excel文件
        /// </summary>
        /// <param name="dataTables">源DataTable和需要合并单元格的数据,如果不需要合并单元格,只需要将CellRangeAddress赋值为null</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">保存位置</param>
        /// <param name="dir">表头文本</param>
        /// <param name="password">加密</param>
        public static void ExportXlsx(Dictionary<DataTable, List<NPOI.SS.Util.CellRangeAddress>> dataTables, string strHeaderText, string strFileName, Dictionary<string, string> dir, string password = null)
        {

            using (MemoryStream ms = ExportXlsx(dataTables, strHeaderText, dir, password))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        /// <summary>
        /// DataTable导出到Excel的MemoryStream xlsx
        /// </summary>
        /// <param name="dataTables">源DataTable和需要合并单元格的数据,如果不需要合并单元格,只需要将CellRangeAddress赋值为null</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        public static MemoryStream ExportXlsx(Dictionary<DataTable, List<NPOI.SS.Util.CellRangeAddress>> dataTables, string strHeaderText, Dictionary<string, string> dir, string password = null)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            int i = 0;
            foreach (var itemTable in dataTables)
            {

                string sheetName = string.IsNullOrEmpty(strHeaderText)
                    ? "Sheet " + (++i).ToString()
                    : strHeaderText + (++i).ToString();
                ISheet sheet = workbook.CreateSheet(sheetName);
                if (string.IsNullOrEmpty(password) == false)
                {
                    sheet.ProtectSheet(password);
                }

                int rowIndex = 0;
                if (itemTable.Key.Columns.Count == 0)
                {
                    continue;

                }
                #region 表头及样式
                {
                    if (!string.IsNullOrEmpty(strHeaderText))
                    {
                        NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        NPOI.SS.UserModel.IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.IsBold = true;
                        headStyle.SetFont(font);
                        headerRow.GetCell(0).CellStyle = headStyle;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, itemTable.Key.Columns.Count - 1));
                        rowIndex += 1;
                    }

                }

                #endregion 表头及样式

                #region 列头及样式
                {
                    //取得列宽
                    int[] arrColWidth = new int[itemTable.Key.Columns.Count];
                    foreach (DataColumn item in itemTable.Key.Columns)
                    {
                        arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                    }
                    for (int k = 0; k < itemTable.Key.Rows.Count; k++)
                    {
                        for (int j = 0; j < itemTable.Key.Columns.Count; j++)
                        {
                            int intTemp = Encoding.GetEncoding(936).GetBytes(itemTable.Key.Rows[k][j].ToString()).Length;
                            if (intTemp > arrColWidth[j])
                            {
                                arrColWidth[j] = intTemp;
                            }
                        }
                    }

                    NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
                    NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    NPOI.SS.UserModel.IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    //font.Boldweight = 700;
                    font.IsBold = true;
                    headStyle.SetFont(font);
                    foreach (DataColumn column in itemTable.Key.Columns)
                    {
                        if (dir != null)
                        {
                            if (dir.ContainsKey(column.ColumnName))
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
                            }
                            else
                            {
                                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            }
                        }
                        else
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        }
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                        //设置列宽
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                    }
                    //headerRow.Dispose();
                    rowIndex += 1;
                }

                #endregion 列头及样式


                #region 内容

                NPOI.SS.UserModel.ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;


                for (int a = 0; a < itemTable.Key.Rows.Count; a++)
                {
                    DataRow dr = itemTable.Key.Rows[a];
                    IRow row = sheet.CreateRow(a + rowIndex);

                    for (int b = 0; b < itemTable.Key.Columns.Count; b++)
                    {
                        row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty);
                        DataColumn dc = itemTable.Key.Columns[b];
                        NPOI.SS.UserModel.ICell newCell = row.CreateCell(dc.Ordinal);
                        newCell.CellStyle = cellStyle;
                        string drValue = dr[b].ToString();
                        SetColumnValue(dc, newCell, drValue);
                    }
                }

                #endregion 内容

                #region 合并单元格
                if (itemTable.Value != null)
                {
                    foreach (var item in itemTable.Value)
                    {
                        sheet.AddMergedRegion(item);
                    }

                }

                #endregion
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                return ms;
            }
        }

        /// <summary>
        /// 获取指定excel单元格数据
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <param name="rownumber">行</param>
        /// <param name="cellnumber">列</param>
        /// <returns></returns>
        public static string GetSpecifyCell(string strFileName, int rownumber, int cellnumber)
        {
            string str = string.Empty;

            IWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                string fileExt = Path.GetExtension(strFileName);
                if (fileExt == ".xls")
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                else if (fileExt == ".xlsx")
                {
                    hssfworkbook = new XSSFWorkbook(file);
                }
                else
                {
                    return str;
                }
                NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
                IRow row = sheet.GetRow(rownumber);
                NPOI.SS.UserModel.ICell cell = row.GetCell(cellnumber);
                str = cell.StringCellValue;
            }
            return str;
        }
        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static byte[] TableToExcel(DataTable dt, string file, string passaord = null)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
            if (workbook == null) { return null; }
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
            sheet.ProtectSheet(passaord);
            //表头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                NPOI.SS.UserModel.ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);

            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    NPOI.SS.UserModel.ICell cell = row1.CreateCell(j);
                    var datatype = dt.Columns[j].DataType;
                    if (datatype.Name == "Decimal")
                    {
                        if (string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
                        {
                            cell.SetCellValue(0);
                        }
                        else
                        {
                            cell.SetCellValue(Convert.ToDouble(dt.Rows[i][j]));
                        }
                    }
                    else if (datatype.Name == "Int32" || datatype.Name == "Int64")
                    {
                        if (string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
                        {
                            cell.SetCellValue(0);
                        }
                        else
                        {
                            cell.SetCellValue(Convert.ToInt32(dt.Rows[i][j]));
                        }
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }


                }
            }
            //转为字节数组
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();
            return buf;
        }
        #endregion
        /// <summary>
        /// 判断内容是否是数字
        /// </summary>
        /// <param name="message"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public static bool isNumeric(String message, out double result)
        {
            Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
            result = -1;
            if (rex.IsMatch(message))
            {
                result = double.Parse(message);
                return true;
            }
            else
                return false;
        }

        /// <summary>
        /// 验证导入的Excel是否有数据
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <returns></returns>
        public static bool HasData(Stream excelFileStream)
        {
            using (excelFileStream)
            {
                IWorkbook workBook = new HSSFWorkbook(excelFileStream);
                if (workBook.NumberOfSheets > 0)
                {
                    ISheet sheet = workBook.GetSheetAt(0);
                    return sheet.PhysicalNumberOfRows > 0;
                }
            }
            return false;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="column"></param>
        /// <param name="newCell"></param>
        /// <param name="drValue"></param>
        /// <returns></returns>
        public static void SetColumnValue(DataColumn column, ICell Cell, string drValue)
        {

            //NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
            //NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat();
            //dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
            string Type = column.DataType.ToString();
            switch (column.DataType.ToString())
            {
                case "System.String": //字符串类型
                    double result;
                    if (isNumeric(drValue, out result))
                    {
                        //数字字符串
                        double.TryParse(drValue, out result);
                        Cell.SetCellValue(result);
                        break;
                    }
                    else
                    {
                        Cell.SetCellValue(drValue);
                        break;
                    }

                case "System.DateTime": //日期类型
                    DateTime dateV;
                    DateTime.TryParse(drValue, out dateV);
                    Cell.SetCellValue(dateV);
                    break;
                case "System.Boolean": //布尔型
                    bool boolV = false;
                    bool.TryParse(drValue, out boolV);
                    Cell.SetCellValue(boolV);
                    break;
                case "System.Int16": //整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(drValue, out intV);
                    Cell.SetCellValue(intV);
                    break;
                case "System.Decimal": //浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(drValue, out doubV);
                    Cell.SetCellValue(doubV);
                    break;
                case "System.DBNull": //空值处理
                    Cell.SetCellValue("");
                    break;
                default:
                    Cell.SetCellValue(drValue.ToString());
                    break;
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    public class NpoiMemoryStream : MemoryStream
    {
        /// <summary>
        /// 
        /// </summary>
        public NpoiMemoryStream()
        {
            AllowClose = true;
        }
        /// <summary>
        /// 
        /// </summary>
        public bool AllowClose { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public override void Close()
        {
            if (AllowClose)
            {
                base.Close();
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    public enum ExcelFileType
    {
        /// <summary>
        /// 
        /// </summary>
        xls = 1,
        /// <summary>
        /// 
        /// </summary>
        xlsx = 2
    }
}
